package db.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import control.ConnectionManage;

import db.parking.beans.SystemDetails;

/**
 * AdminSystemDetails manages all requests From,To table systemDetails
 * @author Operations
 *
 */
public class AdminSystemDetails {

	private static Connection conn = ConnectionManage.getInstance()
			.getConnection();

	/**
	 * Function returns all system details
	 * @return
	 * @throws SQLException
	 */
	public static SystemDetails getOveralSystemDetails() throws SQLException {

		String sql = "select (select count(*) from customer) as Customers,"
				+ "(select count(*) from orders where type=?)as Occasional_orders,"
				+ "(select count(*) from orders where type=?)as Temporary_orders,"
				+ "(select count(*) from canceledorders)as Cancelled_orders,"
				+ "(select count(*) from subscriber)as Subscriptions,"
				+ "(SELECT sum(totalCars) FROM parkinglot where totalCars<>?) as total_cars";

		ResultSet rs = null;

		try (PreparedStatement stmt = conn.prepareStatement(sql)) {
			stmt.setString(1, "Occasional");
			stmt.setString(2, "Temporary");
			stmt.setInt(3, 0);
			rs = stmt.executeQuery();
			if (rs.next()) {
				SystemDetails bean = new SystemDetails();
				bean.setOrdersTemporary(rs.getInt("Temporary_orders"));
				bean.setOrdersOccasional(rs.getInt("Occasional_orders"));
				bean.setCancelledOrders(rs.getInt("Cancelled_orders"));
				bean.setCustomersCount(rs.getInt("Customers"));
				bean.setSubscriptions(rs.getInt("Subscriptions"));
				bean.setCarsAmount(rs.getInt("total_cars"));
				return bean;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
		return null;

	}

}
